Create database lps20
go
use lps20
go
create table quantri(
	maquantri int primary key not null,
	qtmatkhau varchar(20),
	quyen int
);
go

create table hinhthucvay
(
	mahtv int  not null,
	htvten varchar(30) primary key not null

);
go 
Create table tylelai
(
	matll int  not null,
	tllten varchar(30)  not null,
	tllmuclai varchar(30)primary key

);
go 
create table mucphat
(
	mamp int  not null,
	mpsotienlai varchar(30),
	mptyle varchar(30)primary key

	
);
go
Create table nhanvien
(
	manv int primary key not null ,
    nvten varchar(30) not null,
	nvdiachi varchar(30),
	nvsdt int ,
	nvemail varchar(30),
	nvgioitinh char(1)not null
	
);
go
Create table khachhang 
(
	makh int primary key not null ,
	khten varchar(30) ,
	khdiachi varchar(30) ,
	khngaysinh datetime,
	khsdt varchar(15),
	khgioitinh char(1),
	khemail varchar(20),
	khcmt varchar(20),
	khngaytaohoso datetime

	
);
go


Create table hopdongvayvon
(
	mahd int primary key not null ,
	makh int constraint FK1_hopdongvayvon foreign key references khachhang(makh) not null,
	tenkhachhang varchar(30),
	hdvvtongtaisanthechap int,
	hdvvsotienvay int,
	htvten varchar(30) constraint FK2_hopdongvayvon foreign key references hinhthucvay(htvten) not null,
	tllmuclai varchar(30) constraint FK3_hopdongvayvon foreign key references tylelai(tllmuclai) not null,
	hdvvngayvay datetime,
	hdvvngayketthuc datetime	
);
go
create table tralai
(
	matralai int primary key not null,
	ngaydonglai datetime,
	mahd int constraint FK1_tralai foreign key references hopdongvayvon(mahd),
	makh int constraint FK2_tralai foreign key references khachhang(makh) not null,
	tenkhachhang varchar(30),
	sotienvay int,
	htvten varchar(30) constraint FK4_tralai foreign key references hinhthucvay(htvten) not null,
	tllmuclai varchar(30) constraint FK5_tralai foreign key references tylelai(tllmuclai) not null,
	tienlai int
);
go
create table tinhtienphat
(
	matinhtienphat int primary key not null ,
	mahd int constraint FK1_tinhtienphat foreign key references hopdongvayvon(mahd),
	makh int constraint FK2_tinhtienphat foreign key references khachhang(makh) not null,
	tenkhachhang varchar(30),
	sotienvay int,
	htvten varchar(30) constraint FK3_tinhtienphat foreign key references hinhthucvay(htvten) not null,
	tllmuclai varchar(30) constraint FK4_tinhtienphat foreign key references tylelai(tllmuclai) not null,
	mptyle varchar(30) constraint FK5_tinhtienphat foreign key references mucphat(mptyle) not null,
	tienlai int,
	ngayphaidonglai datetime,
	tienphat int
);	
go
insert into quantri values('121','admin','1');
insert into quantri values('122','user','2');

select * from quantri
go
insert into hinhthucvay values('1','Vay mua nha');
insert into hinhthucvay values('2','Vay mua oto');
insert into hinhthucvay values('3','Vay du hoc');
insert into hinhthucvay values('4','Vay ca nhan');
	select * from hinhthucvay
go
insert into tylelai values('1','Vay mua nha','10%');
insert into tylelai values('2','Vay mua oto','8%');
insert into tylelai values('3','Vay du hoc','5%');
insert into tylelai values('4','Vay ca nhan','3%');
	select * from tylelai
go
insert into mucphat values('1','1.000.000--15.000.000','2%');
insert into mucphat values('2','15.100.000 --50.000.000','5%');
insert into mucphat values('3','50.100.000 --100.000.000','8%');
insert into mucphat values('4','100.100.000 --1.000.000.000','3%');
 	select * from mucphat
go
insert into nhanvien values('101','Pham Cong Quan','Nam Dinh','0904717470','quan.nuc3@gmail.com','1');
insert into nhanvien values('102','Vu Dinh Doan','Hai Phong','0904555444','anh.doan@gmail.com','1');
insert into nhanvien values('103','Luc Tuyet Ky','Ha Noi','090437475','hoahong@gmail.com','0');
insert into nhanvien values('104','Truong Tieu Pham','Ha Nam','0904567890','baby@gmail.com','1');
	select * from nhanvien
go
insert into khachhang values('531','Han Lap','Ha Nam','02/02/1990','0912345678','1','mattroi@gmail.com','163097272','05/05/2011');
insert into khachhang values('532','Tan Vu','Ha Noi','03/03/1980','091322324','1','anhnang@gmail.com','163097070','04/09/2011');
insert into khachhang values('533','Nguyen Thi Anh','Ho Chi Minh','04/04/1970','0912345666','0','lekhoc@gmail.com','166697272','07/07/2010');
insert into khachhang values('534','Pham Cong Minh','Da Nang','02/08/1989','0912222678','1','mienbac@gmail.com','164447272','08/08/2009');
insert into khachhang values('535','Pham Ngan Le','Bac Ninh','07/01/1988','0987875678','0','miennam@gmail.com','168097272','05/08/2008');
insert into khachhang values('536','Vu Hong Tuyet','Thai Binh','06/06/1990','0912376548','0','mientay@gmail.com','163099972','01/06/2010');
insert into khachhang values('537','Vu Dinh Tri Giao','Ha Nam','09/09/1993','0914567278','0','mientrung@gmail.com','161111172','02/09/2009');
	select * from khachhang
go
insert into hopdongvayvon values('1001','531','Han Lap','10000000','2000000','vay ca nhan','3%','05/05/2011','05/05/2012');
insert into hopdongvayvon values('1002','532','Tan Vu','20000000','5000000','vay ca nhan ','3%','04/09/2011','04/09/2012');
insert into hopdongvayvon values('1003','533','Nguyen Thi Anh','30000000','7000000','vay du hoc','5%','07/07/2010','07/07/2012');
insert into hopdongvayvon values('1004','534','Pham Cong Minh','40000000','9000000','vay mua nha','10%','08/08/2009','08/08/2012');
insert into hopdongvayvon values('1005','535','Pham Ngan Le','50000000','20000000','vay mua oto','8%','05/08/2008','05/08/2012');
	select * from hopdongvayvon
go
insert into tralai values('111','05/06/2011','1001','531','Han Lap','2000000','vay ca nhan','3%','60000');
insert into tralai values('112','04/10/2011','1002','532','Tan Vu','5000000','vay ca nhan','3%','150000');
insert into tralai values('113','07/08/2010','1003','533','Nguyen Thi Anh','30000000','vay du hoc','5%','1500000');
insert into tralai values('114','08/09/2009','1004','534','Pham Cong Minh','40000000','vay mua nha','10%','4000000');
insert into tralai values('115','05/09/2008','1005','535','Pham Ngan Le','50000000','vay mua oto','8%','4000000');
 select * from tralai
go
insert into tinhtienphat values('0123','1001','531','Han Lap','2000000','vay ca nhan','3%','2%','60000','05/06/2011','1200');
insert into tinhtienphat values('0124','1002','532','Tan Vu','5000000','vay ca nhan','3%','2%','150000','05/06/2011','3000');
insert into tinhtienphat values('0125','1003','533','Nguyen Thi Anh','30000000','vay du hoc','5%','5%','1500000','05/06/2011','75000');
insert into tinhtienphat values('0126','1004','534','Pham Cong Minh','40000000','vay mua nha','10%','5%','4000000','05/06/2011','200000');
insert into tinhtienphat values('0127','1005','535','Pham Ngan Le','50000000','vay mua oto','8%','5%','4000000','05/06/2011','200000');
	select * from tinhtienphat
go